package example.dao;

import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import common.Constants;

@Repository
public class BaseDao {
	protected JdbcTemplate jt;
	protected NamedParameterJdbcTemplate npjt;
	/*
	 * 用注解注入，子类可继承
	 */
	@Autowired
	public void setJt(JdbcTemplate jt) {
		this.jt = jt;
	}
	
	@Autowired
	public void setNpjt(NamedParameterJdbcTemplate npjt) {
		this.npjt = npjt;
	}
	
	/**
	 * 返回分页信息
	 * @param sql sql语句
	 * @param sqlParams sql中参数
	 * @param pageParams 分页相关参数
	 * @return
	 */
	protected Map<String, Object> pagingList(String sql, Object[] sqlParams, Map<String, Object> pageParams) {
		Map<String, Object> pagingInfo = new HashMap<String, Object>();
		
		//查询第几页数据
		int pageNum = (null == pageParams.get("pageNum")) ? 1 : Integer.parseInt(pageParams.get("pageNum").toString());
		//每页显示数量
		int pageSize = (null == pageParams.get("pageSize")) ? Constants.PAGE_SIZE : Integer.parseInt(pageParams.get("pageSize").toString());
		//数据总行数
		int rowCount = jt.queryForObject(" select count(1) from ( " + sql + "  ) ", Integer.class, sqlParams);
		//总页数
		int pageCount = (0 == rowCount%pageSize) ? (rowCount/pageSize) : (rowCount/pageSize + 1);
		
		//控制查询页码范围
		if(pageNum > pageCount) {
			pageNum = pageCount;
		}
		if(1 > pageNum) {
			pageNum = 1;
		}
		
		//计算分页查询范围
		int max = pageNum * pageSize;
		int min = pageSize * (pageNum - 1);
		if(pageNum * pageSize > rowCount) {
			max = rowCount;
		}
		
		/* 
		 * Oracle分页写法
		 * 	SELECT * FROM (
       	 *		SELECT ROW_.*, ROWNUM ROWNUM_ FROM (
         *     		SELECT * FROM emp  ---- 查询语句
       	 *		) ROW_ WHERE ROWNUM <= 20
		 *	) WHERE ROWNUM_ > 10;
		 */
		
		StringBuffer pagingSql = new StringBuffer("");
		pagingSql.append(" SELECT * FROM ( ");
		pagingSql.append("     SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( ");
		pagingSql.append(sql);
		pagingSql.append("     ) ROW_ WHERE ROWNUM <= " + max);
		pagingSql.append(" ) WHERE ROWNUM_ > " + min + " ");
		
		pagingInfo.put("list", jt.queryForList(pagingSql.toString(), sqlParams));
		pagingInfo.put("pageNum", pageNum);
		pagingInfo.put("pageSize", pageSize);
		pagingInfo.put("rowCount", rowCount);
		pagingInfo.put("pageCount", pageCount);
		
		return pagingInfo;
	}
}
